/* GSExcelToManufacture.java
 *
 * Project/Product: APEP-JZCG
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   peng.ning	2008-10-24	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.hitrust.gs.action;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.struts.upload.FormFile;
import system.imp.DateUtil;
import com.hitrust.gs.bean.GSManufactureBean;
import com.hitrust.gs.bean.GSProductUpdatePriceBean;
import com.hitrust.gs.bean.GSProviderBean;
import com.hitrust.gs.business.GSProviderProcessor;
import com.hitrust.gs.dao.GSManufactureDAO;
import com.hitrust.gs.form.GSImportExcelForm;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.service.GSBaseAction;
import com.hitrust.gs.service.GSBaseForm;
import com.hitrust.gs.util.RegulaUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.page.ResultProcess;
import com.hitrust.gs.vo.GSProductUpdatePriceView;

public class GSExcelToProductUpdatePrice extends GSBaseAction {

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.apep.jzcg.service.GSBaseAction#execute(com.apep.jzcg.service.GSBaseForm
	 * )
	 */

	public static final String SHEET_NAME = "产品调价信息";
	public static final String SEP = "%";
	public static String[] HEAD_NAMES = { "序号", "集中编码", "调价前价格", "调价后价格",
			"启用日期","启用时间(小时)","启用时间(分钟)" };

	public boolean[] COL_CHECK = new boolean[7];

	public GSProductUpdatePriceView manuview = null;
	GSProviderProcessor ppro = new GSProviderProcessor();

	// 匹配不包括 ',",<,>,&的特殊字符
	public static final String regula = "[^'\"<>&]*";
	public static final String zhibao = "^\\d+$";


	public String execute(GSBaseForm aForm) throws Exception {
		GSImportExcelForm tGSImportExcelForm = (GSImportExcelForm) aForm;
		FormFile formFile = tGSImportExcelForm.getExcelfile();

		if (formFile != null) {
			ArrayList tList = loadDataFromExcel(formFile.getInputStream(),aForm);
			ResultProcess tProc = new ResultProcess();
			tProc.setResult(tList);
			aForm.getSession().setAttribute("productUpdatePriceExcel", tProc);
		}
		return GSBaseConstants.FORWARD_SUCCESS;
	}

	/**
	 * 从Excel中载入格式化数据，数据项之间以分隔符%隔开
	 * 
	 * @param aInputStream
	 * @param aPage
	 * @param aPageSize
	 * @return
	 * @throws Exception
	 */
	public ArrayList loadDataFromExcel(InputStream aInputStream,GSBaseForm aForm)
			throws Exception {

		ArrayList tPageDataList = new ArrayList();
		ArrayList concentricityCodeList = new ArrayList();
		Workbook tExcelWorkbook = null;

		try {
			tExcelWorkbook = Workbook.getWorkbook(aInputStream);

			Sheet tExcelSheet = tExcelWorkbook.getSheet(SHEET_NAME);
			if (tExcelSheet == null) {
				aForm.getRequest().setAttribute("gohistory","true");	
				throw new GSException("批量导入产品调价信息失败:文件格式不正确,无法找到：" + SHEET_NAME);
			}
			for (int i = 1; i < tExcelSheet.getRows(); i++) {
				Cell tCells[] = tExcelSheet.getRow(i);
				String tRowText = "";
				int num = tCells.length;
				for (short j = 0; j < num; j++) {
					Cell tExcelCell = tCells[j];
					if (tExcelCell == null) {
						break;
					}
					String tCellValue = tExcelCell.getContents();
					if (tCellValue.length() == 0) {
						tCellValue = " ";
					}
					tRowText += tCellValue + SEP;
				}

				for (int a = num; a < HEAD_NAMES.length; a++) {
					tRowText += " " + SEP;
				}

				// 检查数据是否正确,如果和数据库中不符则将结果添加到末尾,从第二行开始
				
				tRowText = this.checkManufacture(tRowText, i - 1,concentricityCodeList);
				for (int tJ = 0; tJ < COL_CHECK.length; tJ++) {
					if (COL_CHECK[tJ] == false) {
						aForm.getRequest().setAttribute("gohistory","true");	
						throw new GSException(tRowText);
					}
				}

				tPageDataList.add(manuview);
				GSLogWriter.info(tRowText);
			}
		} catch (BiffException e) {
			GSLogWriter.error(e.getMessage());
		} catch (IOException e) {
			GSLogWriter.error(e.getMessage());
		}
		finally {
			if (tExcelWorkbook != null)
				tExcelWorkbook.close();
		}
		return tPageDataList;
	}

	/**
	 * 
	 I.产品不可采购或无效不能调价 II．产品供应商无效不能调价
	 * III．调价前价格和条件后价格不能一致且价格不能为负数。同时调价前价格验证和系统内的产品当前价格是否一致 IIII．
	 * 启用日期必须大于当前日期且格式符合要求 IV.同一产品在相同启用日期里，如果存在待审批或者审批通过的记录，提示在启用日期里已经存在相同记录。 序号
	 * 集中编码 调价前价格 调价后价格 启用日期(格式例如: 2011-09-01)
	 * 同一次导入产品编码不能重复
	 **/

	public String checkManufacture(String aRowText, int a,ArrayList concentricityCodeList) throws Exception {
		manuview = new GSProductUpdatePriceView();
		for (int i = 0; i < COL_CHECK.length; i++) {
			COL_CHECK[i] = true;
		}

		String[] tRecorderColumns = aRowText.split(SEP);

		for (int i = 0; i < tRecorderColumns.length; i++) {
			if (!RegulaUtil.matchByString(tRecorderColumns[i], regula)) {
				COL_CHECK[i] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[i] + "不能为',\",<,>,&等特殊字符!";
			}
		}

		if (tRecorderColumns[1].trim().equals("")) {
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[1]
					+ "不能为空！";
		}

		if (tRecorderColumns[4].trim().equals("")) {
			COL_CHECK[4] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[4]
					+ "不能为空！";
		}

		GSManufactureDAO dao = new GSManufactureDAO();
		GSManufactureBean mbean = dao.getManufactureByConcentricityCode(
				tRecorderColumns[1], " select * from t_product ");
		if (mbean == null) {
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[1]
					+ "产品不存在！";
		}
		if(concentricityCodeList.contains(tRecorderColumns[1].trim())){
			COL_CHECK[1] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " 
					+ "同一次导入"+ HEAD_NAMES[1]+"不能重复!";
		}

		if ("".equals(tRecorderColumns[2].trim())) {
			COL_CHECK[2] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[2]
					+ "不能为空!";
		} else {
			Pattern pattern = Pattern.compile("^[0-9]*\\.?[0-9]{0,4}");
			Matcher isNum = pattern.matcher(tRecorderColumns[2].trim());
			if (!isNum.matches()) {
				COL_CHECK[2] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[2] + "必须为数值!";
			} else if (Double.parseDouble(tRecorderColumns[2].trim()) <0) {
				COL_CHECK[2] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[2] + "不能小于0!";
			}
		}

		if ("".equals(tRecorderColumns[3].trim())) {
			COL_CHECK[3] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[3]
					+ "不能为空!";
		} else {
			Pattern pattern = Pattern.compile("^[0-9]*\\.?[0-9]{0,4}");
			Matcher isNum = pattern.matcher(tRecorderColumns[3].trim());
			if (!isNum.matches()) {
				COL_CHECK[3] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[3] + "必须为数值!";
			} else if (Double.parseDouble(tRecorderColumns[3].trim()) <0) {
				COL_CHECK[3] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[3] + "不能小于0!";
			}
		}

		if (tRecorderColumns[2].trim().equals(tRecorderColumns[3].trim())) {
			COL_CHECK[2] = false;
			COL_CHECK[3] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[2]
					+ "与" + HEAD_NAMES[3] + "不能一致!";
		}

		if (mbean != null) {
			String unitPrice=mbean.getGs_manufacture_unitprice() + "";
			String excelPrice=tRecorderColumns[2].trim();
			double price=Double.parseDouble(unitPrice);
			double exPrice=Double.parseDouble(excelPrice);
			if (price!=exPrice) {
				COL_CHECK[2] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[2] + "与产品单价不一致!";
			}
		}
		
		if ("".equals(tRecorderColumns[4].trim())) {
			COL_CHECK[4] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[4]
					+ "不能为空!";
		} else {
			String dateStr="^(?:(?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|" +
			"(?:0[13-9]|1[0-2])-(?:29|30)|(?:0[13578]|1[02])-31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])" +
			"|(?:0[48]|[2468][048]|[13579][26])00)-02-29)$";
			
			Pattern pattern = Pattern.compile(dateStr);
			Matcher isDate = pattern.matcher(tRecorderColumns[4].trim());
			if (!isDate.matches()) {
				COL_CHECK[4] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
						+ HEAD_NAMES[4] + "格式不正确!";
			}
		}
		String date=DateUtil.splitSpecChar(tRecorderColumns[4].trim());
		long dateNum=Long.parseLong(date);
		String curDate=DateUtil.getCurrentDate("yyyyMMdd");
		long curDateNum=Long.parseLong(curDate);
		
		if(dateNum<curDateNum){
			COL_CHECK[4] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
			+ HEAD_NAMES[4] + "不能比当前日期早!";
		}
		
		GSProductUpdatePriceBean pubean = dao.getProductUpdatePriceByConcentricityCodeAndDate(
				tRecorderColumns[1], tRecorderColumns[4]," select * from t_product_update_price ");
		
		if(pubean!=null){
			if("0".equals(pubean.getGs_auditing_status())||"1".equals(pubean.getGs_auditing_status())){
				COL_CHECK[4] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": "
				+ HEAD_NAMES[4] + "里已经存在相同记录!";
			}
		}
		
		if (tRecorderColumns[5].trim().equals("")) {
			COL_CHECK[5] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[5]
					+ "不能为空！";
		}else{
			Pattern pattern = Pattern.compile("([1-9][0-9]*)|(0)");
			Matcher isNum = pattern.matcher(tRecorderColumns[5].trim());
			if (!isNum.matches()) {
				COL_CHECK[5] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[5] + ":请输入正整数！<br>";
			}else{
				if(Integer.parseInt(tRecorderColumns[5].trim())>23){
					COL_CHECK[5] = false;
					return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[5] + ":必须在0-23范围内<br>";
				}
			}
		
		}
		
		if (tRecorderColumns[6].trim().equals("")) {
			COL_CHECK[6] = false;
			return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[6]
					+ "不能为空！";
		}else{
			Pattern pattern = Pattern.compile("([1-9][0-9]*)|(0)");
			Matcher isNum = pattern.matcher(tRecorderColumns[5].trim());
			if (!isNum.matches()) {
				COL_CHECK[6] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[6] + ":请输入正整数！<br>";
			}else{
				if(Integer.parseInt(tRecorderColumns[6].trim())>59){
					COL_CHECK[6] = false;
					return HEAD_NAMES[0] + tRecorderColumns[0] + ": " + HEAD_NAMES[6] + ":必须在0-59范围内<br>";
				}
			}
		
		}
		
		if(dateNum==curDateNum){
			String hour=DateUtil.getCurrentDate("HH");
			if(Integer.parseInt(tRecorderColumns[5].trim())<Integer.parseInt(hour)){
				COL_CHECK[5] = false;
				return HEAD_NAMES[0] + tRecorderColumns[0] + ": 启用时间:启用日期为当天时,启用时间必须大于当前时间<br>";
			}
			String minute=DateUtil.getCurrentDate("mm");
			if(Integer.parseInt(tRecorderColumns[5].trim())==Integer.parseInt(hour)){
				if(Integer.parseInt(tRecorderColumns[6].trim())<=Integer.parseInt(minute)){
					COL_CHECK[6] = false;
					return HEAD_NAMES[0] + tRecorderColumns[0] + ": 启用时间:启用日期为当天时,启用时间必须大于当前时间<br>";
				}
			}
		}
		
		
		
	    GSProviderBean pbean = ppro.queryProviderByShortNameOrId(mbean.getGs_provider_id());

		manuview.setGs_id(tRecorderColumns[0].trim());
		manuview.setGs_concentricity_code(tRecorderColumns[1].trim());
		manuview.setGs_before_price(tRecorderColumns[2].trim());
		manuview.setGs_after_price(tRecorderColumns[3].trim());
		manuview.setGs_enabled_time(tRecorderColumns[4].trim());
		manuview.setGs_recorder_id(String.valueOf(a));//对象编号
		manuview.setGs_product_name(mbean.getGs_manufacture_name());
		manuview.setGs_spec(mbean.getGs_manufacture_spec());
		manuview.setGs_model(mbean.getGs_manufacture_type());
		manuview.setGs_drawing_piece_code(mbean.getGs_drawing_peice_code());
		manuview.setGs_provider_name(pbean.getGs_provider_shortname());
		manuview.setGs_hour(tRecorderColumns[5].trim().length()==1?"0"+tRecorderColumns[5].trim():tRecorderColumns[5].trim());
		manuview.setGs_minute(tRecorderColumns[6].trim().length()==1?"0"+tRecorderColumns[6].trim():tRecorderColumns[6].trim());
		concentricityCodeList.add(tRecorderColumns[1].trim());
		
		return aRowText + "匹配成功！";
	}
}
